/*
Copyright 2012-2018 Jose Robson Mariano Alves

This file is part of bgfinancas.

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This package is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.

*/

package io.github.badernageral.bgfinancas.biblioteca.banco;

import io.github.badernageral.bgfinancas.biblioteca.sistema.Janela;
import io.github.badernageral.bgfinancas.biblioteca.utilitario.Datas;
import io.github.badernageral.bgfinancas.idioma.Linguagem;
import io.github.badernageral.bgfinancas.modelo.Agenda;
import io.github.badernageral.bgfinancas.modelo.Configuracao;
import io.github.badernageral.bgfinancas.modelo.Conta;
import io.github.badernageral.bgfinancas.modelo.Despesa;
import io.github.badernageral.bgfinancas.modelo.Grupo;
import io.github.badernageral.bgfinancas.modelo.Receita;
import io.github.badernageral.bgfinancas.modelo.Transferencia;
import java.sql.SQLException;
import java.time.LocalDate;

public final class Database {
    
    private Database(){ }
    
    public static void verificarBanco() {
        try {
            Conexao banco = Conexao.getInstance();
            banco.executeQuery("SELECT COUNT(TABLE_NAME) AS quantidade FROM INFORMATION_SCHEMA.SYSTEM_TABLES where TABLE_TYPE='TABLE'");
            banco.getResultSet().next();
            if (banco.getResultSet().getInt("quantidade") < 16) {
                Database.criarBanco();
            }
            Database.executarAtualizacoes();
        } catch (SQLException ex) {
            Janela.showException(ex);
        }
    }
    
    private static void criarBanco(){
        Conexao banco = Conexao.getInstance();
        banco.executeUpdate("CREATE TABLE agenda (\n" +
                            "  id_agenda INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_tipo INTEGER, \n" +
                            "  descricao VARCHAR_IGNORECASE(100), \n" +
                            "  valor DECIMAL(10,2), \n" +
                            "  data DATE)");
        banco.executeUpdate("CREATE TABLE agenda_tipos (\n" +
                            "  id_tipo INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE contas (\n" +
                            "  id_conta INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100), \n" +
                            "  valor DECIMAL(10,2), \n" +
                            "  ativada INTEGER, \n" +
                            "  saldo_total VARCHAR_IGNORECASE(10))");
        banco.executeUpdate("CREATE TABLE despesas (\n" +
                            "  id_despesa INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_conta INTEGER, \n" +
                            "  id_item INTEGER, \n" +
                            "  quantidade DECIMAL(10,2), \n" +
                            "  valor DECIMAL(10,2), \n" +
                            "  data DATE, \n" +
                            "  hora TIME)");
        banco.executeUpdate("CREATE TABLE despesas_categorias (\n" +
                            "  id_categoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE despesas_itens (\n" +
                            "  id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_categoria INTEGER, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE planejamento (\n" +
                            "  id_planejamento INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  mes INTEGER, \n" +
                            "  ano INTEGER, \n" +
                            "  valor DECIMAL(10,2))");
        banco.executeUpdate("CREATE TABLE planejamento_componentes (\n" +
                            "  id_planejamento INTEGER, \n" +
                            "  id_item INTEGER, \n" +
                            "  valor DECIMAL(10,2))");
        banco.executeUpdate("CREATE TABLE planejamento_itens (\n" +
                            "  id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE receitas (\n" +
                            "  id_receita INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_conta INTEGER, \n" +
                            "  id_item INTEGER, \n" +
                            "  descricao VARCHAR_IGNORECASE(100), \n" +
                            "  valor DECIMAL(10,2), \n" +
                            "  data DATE, \n" +
                            "  hora TIME)");
        banco.executeUpdate("CREATE TABLE receitas_categorias (\n" +
                            "  id_categoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE receitas_itens (\n" +
                            "  id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_categoria INTEGER, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE relatorios_grupos (\n" +
                            "  id_relatorios_grupos INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100), \n" +
                            "  valor DECIMAL(10,2), \n" +
                            "  saldo_total VARCHAR_IGNORECASE(10))");
        banco.executeUpdate("CREATE TABLE relatorios_grupos_itens (\n" +
                            "  id_relatorios_grupos INTEGER, \n" +
                            "  id_despesas_categorias INTEGER)");
        banco.executeUpdate("CREATE TABLE transferencias (\n" +
                            "  id_transferencia INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_conta1 INTEGER, \n" +
                            "  id_conta2 INTEGER, \n" +
                            "  id_item INTEGER, \n" +
                            "  descricao VARCHAR_IGNORECASE(100), \n" +
                            "  valor DECIMAL(10,2), \n" +
                            "  data DATE, \n" +
                            "  hora TIME)");
        banco.executeUpdate("CREATE TABLE transferencias_categorias (\n" +
                            "  id_categoria INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE transferencias_itens (\n" +
                            "  id_item INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  id_categoria INTEGER, \n" +
                            "  nome VARCHAR_IGNORECASE(100))");
        banco.executeUpdate("CREATE TABLE usuarios (\n" +
                            "  id_usuarios INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                            "  nome VARCHAR_IGNORECASE(100), \n" +
                            "  usuario VARCHAR_IGNORECASE(100), \n" +
                            "  senha VARCHAR_IGNORECASE(100))");               
    }
    
    private static void executarAtualizacoes(){
        Configuracao.verificar();
        Conexao banco = Conexao.getInstance();
        String versao = Configuracao.getPropriedade("versao");
        if(versao.equals("3.0")){
            banco.executeUpdate("ALTER TABLE despesas ADD COLUMN agendada INTEGER DEFAULT 0 NOT NULL");
            banco.executeUpdate("ALTER TABLE despesas ADD COLUMN parcela VARCHAR_IGNORECASE(10)");
            banco.executeUpdate("DROP TABLE planejamento_componentes");
            banco.executeUpdate("DROP TABLE planejamento_itens");
            banco.executeUpdate("DROP TABLE planejamento");
            versao = setValorVersao("3.1");
        }
        if(versao.equals("3.1")){
            versao = setValorVersao("3.1.1");
        }
        if(versao.equals("3.1.1")){
            versao = setValorVersao("3.2");
        }
        if(versao.equals("3.2")){
            versao = setValorVersao("3.2.1");
        }
        if(versao.equals("3.2.1")){
            banco.executeUpdate("CREATE TABLE cartao_credito (\n" +
                                "  id_cartao_credito INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, \n" +
                                "  nome VARCHAR_IGNORECASE(100), \n" +
                                "  limite DECIMAL(10,2), \n" +
                                "  vencimento INTEGER)");
            banco.executeUpdate("ALTER TABLE despesas ADD COLUMN id_cartao_credito INTEGER DEFAULT NULL");
            versao = setValorVersao("3.3");
        }
        if(versao.equals("3.3")){
            versao = setValorVersao("3.4");
        }
        if(versao.equals("3.4")){
            versao = setValorVersao("3.5");
        }
        if(versao.equals("3.5")){
            versao = setValorVersao("3.6");
        }
        if(versao.equals("3.6")){
            banco.executeUpdate("ALTER TABLE cartao_credito ADD COLUMN ativado INTEGER DEFAULT 1");
            versao = setValorVersao("3.6.1");
        }
        if(versao.equals("3.6.1")){
            banco.executeUpdate("ALTER TABLE receitas ADD COLUMN agendada INTEGER DEFAULT 0 NOT NULL");
            banco.executeUpdate("ALTER TABLE receitas ADD COLUMN parcela VARCHAR_IGNORECASE(10)");
            versao = setValorVersao("3.7");
        }
        if(versao.equals("3.7")){
            versao = setValorVersao("3.7.1");
        }
    }
    
    private static String setValorVersao(String versao){
        Configuracao.setPropriedade("versao", versao);
        return versao;
    }
    
    public static void popularBanco(){
        Conexao banco = Conexao.getInstance();
        String data = Datas.getDataSqlHoje();
        if(Configuracao.getPropriedade("idioma").equals("en_US")){
            if(new Agenda().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO AGENDA VALUES(0,0,'Tom',100.00,'"+data+"')");
                banco.executeUpdate("INSERT INTO AGENDA VALUES(1,0,'Harry',50.00,'"+data+"')");
                banco.executeUpdate("INSERT INTO AGENDA_TIPOS VALUES(0,'Accounts receivable')");
            }
            if(new Conta().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO CONTAS VALUES(0,'Bank',500.00,1,'0')");
                banco.executeUpdate("INSERT INTO CONTAS VALUES(1,'Wallet',100.00,1,'0')");
                banco.executeUpdate("INSERT INTO CONTAS VALUES(2,'Savings',200.00,1,'1')");
            }
            if(new Despesa().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(0,'Car')");
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(1,'Essentials')");
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(2,'Recreation')");
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(3,'Snacks')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(0,0,'Gasoline')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(1,0,'Maintenance')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(2,1,'Rice')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(3,1,'Bean')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(4,2,'Cinema')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(5,3,'Donut')");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(0,1,0,20.00,60.00,'"+data+"','08:18:04',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(2,0,2,1.00,10.00,'"+data+"','08:19:47',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(3,0,3,1.00,5.00,'"+data+"','08:20:08',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(4,1,4,2.00,30.00,'"+data+"','08:20:41',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(5,1,5,1.00,3.00,'"+data+"','08:21:59',0,NULL,NULL)");
            }
            if(new Despesa().setSomenteAgendamento().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(1,1,1,1.00,100.00,'"+data+"','08:19:01',1,NULL,NULL)");
            }
            if(new Receita().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(0,'Salary')");
                banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(1,'Others')");
                banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(0,0,'Company X')");
                banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(1,1,'Services')");
                banco.executeUpdate("INSERT INTO RECEITAS VALUES(0,0,0,'"+Linguagem.getInstance().getNomeMes(LocalDate.now().getMonthValue())+" / "+LocalDate.now().getYear()+"',900.00,'"+data+"','15:28:52',0,NULL)");
                banco.executeUpdate("INSERT INTO RECEITAS VALUES(1,1,1,'Repair',50.00,'"+data+"','15:29:55',0,NULL)");
            }
            if(new Grupo().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(0,'Food',200.00,NULL)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(1,'Recreation',100.00,NULL)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(2,'Vehicles',150.00,NULL)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,1)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,3)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(1,2)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(2,0)");
            }
            if(new Transferencia().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(0,0,1,0,'to food',100.00,'"+data+"','08:25:32')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(1,1,2,1,'saving',50.00,'"+data+"','08:26:21')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS_CATEGORIAS VALUES(0,'Bank')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(0,0,'Withdraw')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(1,0,'Deposit')");
            }
        }else{
            if(new Agenda().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO AGENDA VALUES(0,0,'Fulano',100.00,'"+data+"')");
                banco.executeUpdate("INSERT INTO AGENDA VALUES(1,0,'Ciclano',50.00,'"+data+"')");
                banco.executeUpdate("INSERT INTO AGENDA_TIPOS VALUES(0,'Conta a receber')");
            }
            if(new Conta().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO CONTAS VALUES(0,'Banco',500.00,1,'0')");
                banco.executeUpdate("INSERT INTO CONTAS VALUES(1,'Carteira',100.00,1,'0')");
                banco.executeUpdate("INSERT INTO CONTAS VALUES(2,'Poupan\u00e7a',200.00,1,'1')");
            }
            if(new Despesa().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(0,'Carro')");
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(1,'Cesta B\u00e1sica')");
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(2,'Lazer')");
                banco.executeUpdate("INSERT INTO DESPESAS_CATEGORIAS VALUES(3,'Lanches')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(0,0,'Gasolina')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(1,0,'Manuten\u00e7\u00e3o')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(2,1,'Arroz')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(3,1,'Feij\u00e3o')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(4,2,'Cinema')");
                banco.executeUpdate("INSERT INTO DESPESAS_ITENS VALUES(5,3,'Pastel')");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(0,1,0,20.00,60.00,'"+data+"','08:18:04',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(2,0,2,1.00,10.00,'"+data+"','08:19:47',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(3,0,3,1.00,5.00,'"+data+"','08:20:08',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(4,1,4,2.00,30.00,'"+data+"','08:20:41',0,NULL,NULL)");
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(5,1,5,1.00,3.00,'"+data+"','08:21:59',0,NULL,NULL)");
            }
            if(new Despesa().setSomenteAgendamento().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO DESPESAS VALUES(1,1,1,1.00,100.00,'"+data+"','08:19:01',1,NULL,NULL)");
            }
            if(new Receita().listar().isEmpty()){                
                banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(0,'Sal\u00e1rio')");
                banco.executeUpdate("INSERT INTO RECEITAS_CATEGORIAS VALUES(1,'Outros')");
                banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(0,0,'Empresa X')");
                banco.executeUpdate("INSERT INTO RECEITAS_ITENS VALUES(1,1,'Servi\u00e7os prestados')");
                banco.executeUpdate("INSERT INTO RECEITAS VALUES(0,0,0,'"+Linguagem.getInstance().getNomeMes(LocalDate.now().getMonthValue())+" / "+LocalDate.now().getYear()+"',900.00,'"+data+"','15:28:52',0,NULL)");
                banco.executeUpdate("INSERT INTO RECEITAS VALUES(1,1,1,'Conserto',50.00,'"+data+"','15:29:55',0,NULL)");
            }
            if(new Grupo().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(0,'Alimenta\u00e7\u00e3o',200.00,NULL)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(1,'Lazer',100.00,NULL)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS VALUES(2,'Ve\u00edculos',150.00,NULL)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,1)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(0,3)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(1,2)");
                banco.executeUpdate("INSERT INTO RELATORIOS_GRUPOS_ITENS VALUES(2,0)");
            }
            if(new Transferencia().listar().isEmpty()){
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(0,0,1,0,'para comida',100.00,'"+data+"','08:25:32')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS VALUES(1,1,2,1,'poupando',50.00,'"+data+"','08:26:21')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS_CATEGORIAS VALUES(0,'Banco')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(0,0,'Saque')");
                banco.executeUpdate("INSERT INTO TRANSFERENCIAS_ITENS VALUES(1,0,'Dep\u00f3sito')");
            }
        }
    }
    
}
